package com.zr.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.dao.StudentDaoHyx;
import com.zr.model.Student;
import com.zr.util.JDBCUtil;

public class StudentDaoImplHyx implements StudentDaoHyx{

	//根据老师ID查找学生总数
	@Override
	public int CountStudentByUid(int tid) {
		int i = 0;
		StringBuffer sql = new StringBuffer("");
		sql.append(" select COUNT(s_t.tid) as ts from student");
		sql.append(" INNER JOIN s_t ON s_t.sid=student.sid");
		sql.append(" INNER JOIN teacher ON teacher.tid=s_t.tid");
		sql.append(" WHERE teacher.tid=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, tid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				i=rs.getInt("ts");
			}
			
			JDBCUtil.closeJDBC(pst, con);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	//查找老师所属学生的信息
	@Override
	public List<Student> selStudentByUid(int tid, int page, int size) {
		List<Student> stus = new ArrayList<Student>();
		StringBuffer sql = new StringBuffer("");
		sql.append(" select student.bid,student.saddress,student.sage,student.sid,");
		sql.append(" student.sname,student.spnumber,student.ssex,student.stime");
		sql.append(" from student INNER JOIN s_t ON s_t.sid=student.sid");
		sql.append(" INNER JOIN teacher ON teacher.tid=s_t.tid WHERE teacher.tid=?");
		sql.append(" limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, tid);
			pst.setInt(2, (page-1)*size);
			pst.setInt(3, size);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Student s =new Student();
				s.setSid(rs.getInt("sid"));
				s.setSage(rs.getString("sage"));
				s.setSname(rs.getString("sname"));
				s.setBid(rs.getInt("bid"));
				s.setSaddress(rs.getString("saddress"));
				s.setSpnumber(rs.getString("spnumber"));
				s.setSsex(rs.getString("ssex"));
				s.setStime(rs.getString("stime"));
				stus.add(s);
			}
			
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return stus;
	}

	//老师修改自己学生的基本信息
	@Override
	public boolean updateTeacherStudent(int sid, String sname, String sage, String ssex, String saddress,
			String spnumber,String stime) {
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE student SET student.sname=?,student.sage=?,");
		sql.append(" student.ssex=?,student.saddress=?,student.spnumber=?,student.stime=?");
		sql.append(" WHERE student.sid=?");
		Connection con = JDBCUtil.getConnection();
	 	try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, sname);
			pst.setString(2, sage);
			pst.setString(3, ssex);
			pst.setString(4, saddress);
			pst.setString(5, spnumber);
			pst.setString(6, stime);
			pst.setInt(7, sid);
			int i = pst.executeUpdate();
			if(i>0){
				flag=true;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 	return flag;
	}

	//所有学生的总数
	@Override
	public int CountAllStudent() {
		int i = 0;
		StringBuffer sql = new StringBuffer("");
		sql.append(" select COUNT(sid) as ts from student");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				i=rs.getInt("ts");
			}
			
			JDBCUtil.closeJDBC(pst, con);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	//所有学生的信息
	@Override
	public List<Student> selectAllStudent(int page, int size) {
		List<Student> stus = new ArrayList<Student>();
		StringBuffer sql = new StringBuffer("");
		sql.append(" select student.bid,student.saddress,student.sage,student.sid,");
		sql.append(" student.sname,student.spnumber,student.ssex,student.stime,banji.bname");
		sql.append(" from student inner join banji on banji.bid=student.bid limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, (page-1)*size);
			pst.setInt(2, size);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Student s =new Student();
				s.setSid(rs.getInt("sid"));
				s.setSage(rs.getString("sage"));
				s.setSname(rs.getString("sname"));
				s.setBid(rs.getInt("bid"));
				s.setSaddress(rs.getString("saddress"));
				s.setSpnumber(rs.getString("spnumber"));
				s.setSsex(rs.getString("ssex"));
				s.setStime(rs.getString("stime"));
				s.setBname(rs.getString("bname"));
				stus.add(s);
			}
			
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return stus;
	}

	//根据老师姓名查找学生总数
	@Override
	public int CountStudentByTname(String tname) {
		int i = 0;
		StringBuffer sql = new StringBuffer("");
		sql.append(" select COUNT(s_t.tid) as ts from student");
		sql.append(" INNER JOIN s_t ON s_t.sid=student.sid");
		sql.append(" INNER JOIN teacher ON teacher.tid=s_t.tid");
		sql.append(" WHERE teacher.tname=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, tname);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				i=rs.getInt("ts");
			}
			
			JDBCUtil.closeJDBC(pst, con);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	//老师姓名下学生的信息
	@Override
	public List<Student> selectStudentByTname(String tname, int page, int size) {
		List<Student> stus = new ArrayList<Student>();
		StringBuffer sql = new StringBuffer("");
		sql.append(" select student.bid,student.saddress,student.sage,student.sid,");
		sql.append(" student.sname,student.spnumber,student.ssex,student.stime");
		sql.append(" from student INNER JOIN s_t ON s_t.sid=student.sid");
		sql.append(" INNER JOIN teacher ON teacher.tid=s_t.tid");
		sql.append(" WHERE teacher.tname=? limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, tname);
			pst.setInt(2, (page-1)*size);
			pst.setInt(3, size);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Student s =new Student();
				s.setSid(rs.getInt("sid"));
				s.setSage(rs.getString("sage"));
				s.setSname(rs.getString("sname"));
				s.setBid(rs.getInt("bid"));
				s.setSaddress(rs.getString("saddress"));
				s.setSpnumber(rs.getString("spnumber"));
				s.setSsex(rs.getString("ssex"));
				s.setStime(rs.getString("stime"));
				stus.add(s);
			}
			
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return stus;
	}
//----------管理员修改学生信息----------
	@Override
	public boolean adminUpdateStudent(int sid, String sname, String sage, String ssex, String saddress, String spnumber,
			String stime, String bname) {
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE student SET student.sname=?,student.sage=?,");
		sql.append(" student.ssex=?,student.saddress=?,student.spnumber=?,student.stime=?,");
		sql.append(" student.bid=(SELECT banji.bid from banji where banji.bname=?)");
		sql.append(" WHERE student.sid=?");
		Connection con = JDBCUtil.getConnection();
	 	try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, sname);
			pst.setString(2, sage);
			pst.setString(3, ssex);
			pst.setString(4, saddress);
			pst.setString(5, spnumber);
			pst.setString(6, stime);
			pst.setString(7, bname);
			pst.setInt(8, sid);
			int i = pst.executeUpdate();
			if(i>0){
				flag=true;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 	return flag;
	}
//-------管理员新增学生信息--------
	@Override
	public boolean adminAddStudent(String sname, String sage, String ssex, String saddress, String spnumber,
			String stime, String bname) {
		boolean flag = false;
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO student(sname,ssex,sage,saddress,spnumber,stime,bid)");
		sql.append(" VALUES(?,?,?,?,?,?,");
		sql.append(" (SELECT banji.bid from banji where banji.bname=?))");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, sname);
			pst.setString(2, ssex);
			pst.setString(3, sage);
			pst.setString(4, saddress);
			pst.setString(5, spnumber);
			pst.setString(6, stime);
			pst.setString(7,bname);
			int i = pst.executeUpdate();
			if(i>0){
				flag=true;
			}
			
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return flag;
	}
//-------获取新增学生的ID------
	@Override
	public Student findNewStudent(String sname, String sage, String ssex, String saddress, String spnumber,
			String stime, String bname) {
		Student s = new Student();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT student.sid FROM student WHERE student.sname=? and student.stime=? and");
		sql.append(" student.sage=? and student.ssex=? and student.saddress=? and student.spnumber=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1,sname );
			pst.setString(2,stime );
			pst.setString(3,sage );
			pst.setString(4,ssex );
			pst.setString(5,saddress );
			pst.setString(6,spnumber );			
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				s.setSid(rs.getInt("sid"));
			}
			
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return s;
	}
//--------给老是增加学生----------
	public boolean adminInsertS_T(int sid,int tid){
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT into s_t(sid,tid) VALUES(?,?)");
		Connection con = JDBCUtil.getConnection();
	 	try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setInt(2, tid);
			int i = pst.executeUpdate();
			if(i>=0){
				flag=true;
			}
			
			JDBCUtil.closeJDBC(pst, con);
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 	return flag;
	}
//--------管理员删除学生信息--------
	@Override
	public boolean adminDeleteStudent(int sid) {
		boolean flag1 = false;
		StringBuffer sql = new StringBuffer();
		sql.append(" delete student from student ");
		sql.append(" where student.sid=?");
		Connection con = JDBCUtil.getConnection();
	 	try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			int i = pst.executeUpdate();
			if(i>0){
				flag1=true;
			}
			
			JDBCUtil.closeJDBC(pst, con);
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		boolean flag2 = new StudentDaoImplHyx().adminDeleteS_C(sid);
		boolean flag3 = new StudentDaoImplHyx().adminDeleteS_T(sid);
		boolean flag =(flag1&&flag2&&flag3);
	 	return flag;
	}
//-----------删除S_C关联----------
	public boolean adminDeleteS_C(int sid) {
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append(" delete  from s_c where s_c.sid=?");
		Connection con = JDBCUtil.getConnection();
	 	try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			int i = pst.executeUpdate();
			if(i>=0){
				flag=true;
			}
			
			JDBCUtil.closeJDBC(pst, con);
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 	return flag;
	}
//-----------删除S_T关联----------
	public boolean adminDeleteS_T(int sid) {
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append(" delete  from s_t where s_t.sid=? ");
		Connection con = JDBCUtil.getConnection();
	 	try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			int i = pst.executeUpdate();
			if(i>=0){
				flag=true;
			}
			
			JDBCUtil.closeJDBC(pst, con);
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 	return flag;
	}

	
}
